<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use DB;

use App\Http\Requests;
use App\Http\Controllers\Controller;

class DatabaseController extends Controller
{
   //采用DB类操作数据库

    public function index()
    {
        echo '############';
        dd(new DB);
        //DB::select('');
    }

    public function select()
    {
        // $result=DB::select("SELECT * FROM man_player WHERE id=?",array(1));
        $result=DB::select("SELECT * FROM man_player WHERE id=:xx",array(':xx'=>3));
        dd($result);
        //print_r($result[0]->id);

        //
        //echo $result[0]->id;
    }

    public function insert()
    {
        $result=DB::insert("INSERT INTO man_player (id,name,sex,age) VALUE (null,'甜甜',0,23)");
        
        //返回真假值
        dd($result);
    }

    public function update()
    {
        $result = DB::update("UPDATE man_player SET name='小康' WHERE id=5");
        //返回受影响的行数
        dd($result);
    }

    public function delete()
    {
        $result = DB::delete("DELETE FROM man_player WHERE id=2");
        //返回受影响行数
        dd($result); 
    }

    public function drop()
    {   //dml dql
        //DCL and DDL
        //$result = DB::statement('drop');
        //dd($result);
    }

    //进行事物操作
    public function trans()
    {
        // //开启事务
        // DB::beginTransaction();
        // //事务操作
        // $result1=DB::update("UPDATE trans SET money=money+500 WHERE id=1");
        // $result2=DB::update("UPDATE trans SET money=money-500 WHERE id=2");

        // //判断操作是否成功
        // if($result1 && $result2){
        //     //提交事务
        //     DB::commit();
        //     echo "转账成功";
        // }else{
        //     DB::rollback();
        //     echo "转账失败";
        // }
        //第二种事务操作
        DB::transaction(function(){
            //使用构造器操作

            //转账给甜甜
            //扣
            //DB::update("UPDATE trans SET money=money-500 where id=1");
            //转
            //DB::update("UPDATE trans SET money=money+500 where id=2");
        });  
    }
    //链接数据库
    public function anotherdb()
    {
        dd(DB::connection('kaka')->select("SELECT * FROM  shop"));
    }


    //查询构造器
    public function insertone()
    {   
        //默认数据库中的man_player表
        $result=DB::table('player')->insertGetId(array('name'=>'neil','sex'=>'nan','age'=>22));
        dd($result);
    }
    //插入duotiao
    public function insertMore()
    {
        $result=DB::table('player')->insert(array(
            array('name'=>'萝莉','sex'=>'0','age'=>14),
            array('name'=>'御姐','sex'=>'0','age'=>34),
        ));
        dd($result);
    }

    //更新操作
    public function QBupdate()
    {
        DB::table('player')->where('name',666)->update(['name'=>'美女']);
    }

    //删除操作
    public function QBdelete()
    {
        $result=DB::table('player')->where('id',1)->delete();
        dd($result);
    }

    //查询单条
    public function QBselect()
    {
        $result= DB::table('player')->where('id',5)->get();
        //$result= DB::table('player')->select('id',5)->get();
        //$result= DB::table('player')->where('id',5)->first();
         dd($result);
    }

    //查询单条中的某个字段
    public function QBselectStr()
    {
        $result=DB::table('player')->where('id',5)->value('name');
        //$result=DB::table('player')->lists('name');
        //$result=DB::table('player')->where('id',3)->lists('name');
        dd($result);
    }

    //查询指定行
    public function getSelect()
    {
        //$result= DB::table('player')->where('id','!=',7)->select('id as n','name as b')->first();
        
        //where 的 逻辑判断 
        //$result= DB::table('player')->where('name','like','%美%')->where('id','>',7)->get();
        //$result = DB::table('player')->where('id','=',3)->where('id','=',5)->get();
        // $result = DB::table('player')->whereBetween('id',[4,7])->get();
        $result = DB::table('player')->whereIn('id',[2,4,5,7,9])->get();
        dd($result);
    }

    //排序操作
    public function getOrder()
    {
        //$result =DB::table('player')->orderBy('id','desc')->get();
        $result =DB::table('player')->orderBy('sex','asc')->orderBy('id','desc')->get();
        dd($result);
    }

    public function getPage()
    {
        //$result = DB::table('player')->skip(7)->take(3)->get();
        
        //$result = DB::table('player')->avg('id');
        dd($result);
    }

    //联合查询
    public function join()
    {
        $result=DB::table('article')
        ->join('player','player.id','=','article.uid')

        ->get();
        
        dd($result);
    }

}
